package com.apt.load;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.apt.common.AptException;
import com.apt.common.DBUtility;
import com.apt.dao.LoadTestDao;
import com.apt.domain.AnswerMaster;
import com.apt.domain.QuestionMaster;
import com.apt.domain.TestMaster;
import com.apt.domain.UserMaster;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class LoadQuestions {

	public static void main(String[] args) {
		LoadQuestions rf = new LoadQuestions();

		String fileName = "Numbers.xls";
		// String fileName = "Avg.xls";
		// String fileName = "temp.xls";

		Connection con = null;

		String userid = "root";
		String password = "password";
		// String url = "jdbc:oracle:thin:@dbss9017.uhc.com:1521:encdv2";
		String url = "jdbc:mysql://127.0.0.1:3306/APTDB";
		// String driverName = "oracle.jdbc.driver.OracleDriver";
		String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver

		String question = null;
		String section = null;
		String correctoption = null;

		int questionid = 0;

		PreparedStatement pqm = null;
		PreparedStatement pam = null;
		PreparedStatement pqms = null;

		int cnt = 0;

		ResultSet rqms = null;
		String qm = "insert into aptdb.question_master(QUESTION,SECTION,TEST_ID) values"
				+ "(?,?,1)";
		String am = "insert into aptdb.answer_master(QUESTION_ID,ANSWER,ANSWER_CORRECT) values "
				+ " (?,?,?)";

		String qms = "select QUESTION_ID from aptdb.question_master where QUESTION=?";

		try {
			Class.forName(driverName);
			con = DriverManager.getConnection(url, userid, password);
			Workbook wb = Workbook.getWorkbook(new File(fileName));
			Sheet s = wb.getSheet(0);

			pqm = con.prepareStatement(qm);
			pam = con.prepareStatement(am);
			pqms = con.prepareStatement(qms);

			for (int i = 1; i < s.getRows(); i++) {
				question = s.getCell(1, i).getContents();
				section = s.getCell(6, i).getContents();

				pqm.setString(1, question);
				pqm.setString(2, section);
				cnt = pqm.executeUpdate();
				if (cnt > 0) {
					pqms.setString(1, question);
					rqms = pqms.executeQuery();
					if (rqms.next()) {
						questionid = rqms.getInt(1);

						for (int j = 2; j <= 5; j++) {
							pam.setInt(1, questionid);
							pam.setString(2, s.getCell(j, i).getContents());
							if (j == 2) {
								correctoption = "A";
							} else if (j == 3) {
								correctoption = "B";
							} else if (j == 4) {
								correctoption = "C";
							} else if (j == 5) {
								correctoption = "D";
							}
							pam.setString(3, (s.getCell(7, i).getContents()
									.equals(correctoption)) ? "Y" : "N");
							pam.executeUpdate();

						}
					}

				}

				/*
				 * System.out.println("value - " + i + ".." + s.getCell(1,
				 * i).getContents()); System.out.println("value - " + i + ".." +
				 * s.getCell(2, i).getContents()); System.out.println("value - "
				 * + i + ".." + s.getCell(3, i).getContents());
				 * System.out.println("value - " + i + ".." + s.getCell(4,
				 * i).getContents()); System.out.println("value - " + i + ".." +
				 * s.getCell(5, i).getContents()); System.out.println("value - "
				 * + i + ".." + s.getCell(6, i).getContents());
				 * System.out.println("value - " + i + ".." + s.getCell(7,
				 * i).getContents());
				 */

			}

		} catch (BiffException be) {
			be.printStackTrace();
		} catch (IOException ie) {
			ie.printStackTrace();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (ClassNotFoundException ce) {
			ce.printStackTrace();
		}

	}

	public void loadQuestions(TestMaster testMaster) throws AptException {
		int testId;
		String question;
		String section;
		int questionId;
		String answer;
		String correctoption = null;
		List<AnswerMaster> answerList = new ArrayList<AnswerMaster>();

		try {
			Workbook wb = Workbook.getWorkbook(testMaster.getInputFile());
			Sheet s = wb.getSheet(0);

			TestMaster tm = new TestMaster(testMaster.getName(),
					testMaster.getTestTypeId(), s.getRows() - 1,
					testMaster.getDisabled(), testMaster.getDuration());
			int success = new LoadTestDao(DBUtility.getConnection())
					.insertTest(tm);
			if (success >= 0) {
				testId = new LoadTestDao(DBUtility.getConnection())
						.getTestId(testMaster.getName());

				for (int i = 1; i < s.getRows(); i++) {
					question = s.getCell(1, i).getContents();
					section = s.getCell(6, i).getContents();
					new LoadTestDao(DBUtility.getConnection())
							.insertQuestion(new QuestionMaster(testId,
									question, section));
					questionId = new LoadTestDao(DBUtility.getConnection()).getQuestionId(question, testId);
					
					for (int j = 2; j <= 5; j++) {
						answer = s.getCell(j, i).getContents();
						if (j == 2) {
							correctoption = "A";
						} else if (j == 3) {
							correctoption = "B";
						} else if (j == 4) {
							correctoption = "C";
						} else if (j == 5) {
							correctoption = "D";
						}
						AnswerMaster am = new AnswerMaster(questionId, answer, (s.getCell(7, i).getContents()
								.equals(correctoption)) ? "Y" : "N");
						
						answerList.add(am);
						new LoadTestDao(DBUtility.getConnection()).insertAnswer(answerList);
						answerList.clear();

					}
					
					
				}
			} else {
				throw new AptException("no valid test id");
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException ie) {
			ie.printStackTrace();
		}
	}
}